
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import javax.swing.JOptionPane;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author William
 */
public class School {
    public int schoolID;
    public String name;
    

 //   public Map<String, Building> buildingMap = new TreeMap<String, Building>();

    //public Map<String, Student> studentMap = new TreeMap<String, Student>();
    //public Map<String, Staff> staffMap = new TreeMap<String, Staff>();

    Statement stmt;
    Connection conn;

    public School() {
        connect();
        //setUpBuildings();
        //setUpStaff();


    }
public void connect()   {
        
try 
        {
            // Load the JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded");

            // Establish a connection
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/DORMS?user=root&password=dorms");
            System.out.println("Database connected");

            // Create a Statement
            stmt = (Statement) conn.createStatement();

        } 
        catch (Exception ex) 
        {
            System.out.println(ex);
        }

}
    
public ArrayList<Building> getBuildings() {
    ArrayList<Building> buildingArrayList = new ArrayList<Building>();

    String query = "Select * from DORMS.building";
    try {
            ResultSet rset = stmt.executeQuery(query);
         while (rset.next())
            {
                
              int id = rset.getInt(1);
               
                Building building = new Building(id,conn);
                      
                buildingArrayList.add(building);
            }   
    } catch(SQLException E) {
        
    }
        
    return buildingArrayList;
   
}
public ArrayList<Student> getAllStudents() {
    ArrayList<Student> studentList = new ArrayList<Student>();

    String query = "Select studentID from DORMS.student";
    try {
        ResultSet rset = stmt.executeQuery(query);
        while (rset.next())
        {
              
              int id = rset.getInt(1);
              Student student = new Student(id,conn);                     
              studentList.add(student);
                       
        }
    } catch(SQLException E) {
    }
    return studentList;
    
}

/*
public ArrayList<Staff> getStaff() {
    ArrayList<Staff> staffList = new ArrayList<Staff>();

    String query = "SELECT staffID FROM DORMS.staff";

    try {
        ResultSet rset = stmt.executeQuery(query);
        while (rset.next())
        {
              
              int id = rset.getInt(1);
              Staff staff = new Staff(id,conn);                     
              staffList.add(staff);
                       
        }
    } catch(SQLException E) {
    }
    return staffList;
    

    
    
}

 * 
 */
public Object[] setUpNotifications() {
    ArrayList<Request> notificationList = new ArrayList<Request>();


    String query = "Select * from DORMS.request where status != 2 AND status != 3";
    try {
        ResultSet rset = stmt.executeQuery(query);
        while (rset.next())
        {
            int id = rset.getInt(1);
            Request request = new Request(id,conn);
            notificationList.add(request);
        }
                
    } catch(SQLException e) {
    
    }
    return notificationList.toArray();

    
    
}



public ArrayList<Room> getAvailableRooms(int type){
    ArrayList<Room> rooms = new ArrayList<Room>();
    
    String query = "SELECT * FROM DORMS.room r join DORMS.building b "
            + "WHERE r.buildingID = b.buildingID AND b.type = ? AND r.type = ? AND isAvail = 1 AND forFreshman = 0";
    try{
        PreparedStatement queryStmt = conn.prepareStatement(query);
        queryStmt.setInt(1, Building.TYPE_DORM);
        queryStmt.setInt(2, type);
        ResultSet result = queryStmt.executeQuery();
        while(result.next()){
            rooms.add(new Room(result.getInt("roomID"),conn));
        }
        
    }catch(SQLException e){
        
    }
    
    return rooms;
}

public ArrayList<Room> getUnavailableRooms(int type){
    ArrayList<Room> rooms = new ArrayList<Room>();
    
    String query = "SELECT * FROM DORMS.room r join DORMS.building b "
            + "WHERE r.buildingID = b.buildingID AND b.type = ? AND r.type = ? AND (isAvail = 0 OR forFreshman = 1)";
    try{
        PreparedStatement queryStmt = conn.prepareStatement(query);
        queryStmt.setInt(1, Building.TYPE_DORM);
        queryStmt.setInt(2, type);
        ResultSet result = queryStmt.executeQuery();
        while(result.next()){
            rooms.add(new Room(result.getInt("roomID"),conn));
        }
        
    }catch(SQLException e){
        
    }
    
    return rooms;
}

private ArrayList<Room> getUnavailableBySubtraction(int type){
    ArrayList<Room> allRooms = allRoomsInType(type);
    ArrayList<Room> availableRooms = getAvailableRooms(type);
            
    for(int i = 0; i < allRooms.size();i++){
        for(int j = 0; j < availableRooms.size(); j++){
            if(availableRooms.get(j).roomID == allRooms.get(i).roomID){
                allRooms.remove(i);
                i--;
                break;
            }
        }
        
    }
    
    
    return allRooms;
}

private ArrayList<Room> allRoomsInType(int type){
    ArrayList<Room> allRoomsInType = new ArrayList<Room>();
    
    String query = "SELECT roomID FROM DORMS.room WHERE type = ?";
    try{
        PreparedStatement queryStmt = conn.prepareStatement(query);
        queryStmt.setInt(1, type);
        ResultSet result = queryStmt.executeQuery();
        while(result.next()){
            allRoomsInType.add(new Room(result.getInt("roomID"),conn));
        }
        
        
    }catch(SQLException e){
        
    }
    
    return allRoomsInType;
}

public ArrayList<Room> filterByBuilding(ArrayList<Room> list,int buildingID){
    ArrayList<Room> filtered = new ArrayList<Room>();
    
    for(int i =0; i < list.size(); i++){
        if(list.get(i).buildingID == buildingID)
            filtered.add(list.get(i));
    }
    
    return filtered;
    
    
}


 public boolean setHousingSelection(int year, int type, Calendar startDateTime,Calendar endDateTime){
     String query = "INSERT INTO DORMS.housingSelection VALUES (?,?,?,?)";
     try{
        PreparedStatement queryStmt = conn.prepareStatement(query);
        queryStmt.setInt(1,year);
        queryStmt.setInt(2,type);
        queryStmt.setTimestamp(3,new Timestamp(startDateTime.getTimeInMillis()));
        queryStmt.setTimestamp(4,new Timestamp(endDateTime.getTimeInMillis()));
        queryStmt.execute();
        return true;
        
     }catch(SQLException e){
        JOptionPane.showMessageDialog(null, "Setup failed, please check for duplicates.");

     }
     return false;
     
 }


}
